﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ExcelDna.Integration;

namespace ExcelDnaDemo
{
    public static class ExcelCommands
    {

        [ExcelCommand(MenuName = "Demo Tools", MenuText = "单元格选区平方")]
        public static void SquareRange()
        {
            object[,] result;

            // Get a reference to the current selection
            ExcelReference selection = null; ;
            try
            {
                selection = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
            }
            catch
            {
                return;
            }
            if (selection == null) return;

            dynamic obj = ReferenceToRange(selection);
            //if (!(obj is ExcelPIA.Range)) return;

            // Get the value of the selection
            object selectionContent = selection.GetValue();
            if (selectionContent is object[,])
            {
                object[,] values = (object[,])selectionContent;
                int rows = values.GetLength(0);
                int cols = values.GetLength(1);
                result = new object[rows, cols];

                // Process the values
                for (int i = 0; i < rows; i++)
                {
                    for (int j = 0; j < cols; j++)
                    {
                        double val = (double)values[i, j];
                        result[i, j] = val * val;                         
                    }
                }
            }
            else if (selectionContent is double)
            {
                double value = (double)selectionContent;
                result = new object[,] { { value * value } };
            }
            else
            {
                result = new object[,] { { "Selection was not a range or a number, but " + selectionContent.ToString() } };
            }

            // Now create the target reference that will refer to Sheet 1, getting a reference that contains the SheetId first
            //ExcelReference sheet = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, "Sheet1"); // Throws exception if no Sheet1 exists
            // ... then creating the reference with the right size as new ExcelReference(RowFirst, RowLast, ColFirst, ColLast, SheetId)
            int resultRows = result.GetLength(0);
            int resultCols = result.GetLength(1);
            ExcelReference target = new ExcelReference(0, resultRows - 1, 0, resultCols - 1);
            // Finally setting the result into the target range.
            target.SetValue(result);
        }

        /// <summary>
        /// menu command
        /// </summary>
        [ExcelCommand(MenuName = "Demo Tools", MenuText = "单元格选区求和到A1")]
        public static void SumRange()
        {
            //xlcall.h
            //https://code.msdn.microsoft.com/Excel-2010-Writing-791e9222/sourcecode?fileId=25565&pathId=1844590411

            // Get a reference to the current selection
            ExcelReference selection = null; ;
            try
            {
                selection = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
            }
            catch
            {
                return;
            }
            if (selection == null) return;

            object sum = XlCall.Excel(XlCall.xlfSum, selection);

            ExcelReference target = new ExcelReference(0, 0, 0, 0);  //activesheet.cells[1,1]
            target.SetValue(sum);

            //use PIA
            //ExcelPIA.Range dataRange = ReferenceToRange(selection) as ExcelPIA.Range;

            //dataRange = dataRange.Cells[1, 1];
            //dataRange.set_Value(ExcelPIA.XlRangeValueDataType.xlRangeValueDefault, sum);

            //Range to Array
            //object selectionContent = selection.GetValue();
            //object[,] values = (object[,])selectionContent;
        }
        [ExcelCommand(MenuName = "Demo Tools", MenuText = "TestXlcNew", Name = "TestXlcNew")]
        public static void TestXlcNew()
        {
            //1	one worksheet  
            //2 one chartsheet  
            //3 one Excel 4.0 macro sheet
            //4 one Excel 4.0 international macro sheet
            //5	New workbook with 16 worksheets or based on the default workbook
            //6	New workbook with one Visual Basic module
            //7	one dialog sheet
            XlCall.Excel(XlCall.xlcNew, 1);
            //XlCall.Excel(XlCall.xlcWorkbookInsert, 6);
        }

        [ExcelCommand(MenuName = "Demo Tools", MenuText = "TestCAPI", Name = "TestCAPI")]
        public static void TestCAPI()
        {
            //https://msdn.microsoft.com/zh-cn/library/aa730920.aspx Developing Add-ins (XLLs) in Excel 2007
            object ret;

            //https://groups.google.com/forum/?fromgroups#!searchin/exceldna/isexceptionsafe/exceldna/W7pVEyrNf9o/sLslHcd3KXoJ
            XlCall.Excel(XlCall.xlAbort, true, 1, true);  //detect <Escape>
                                                          //                                    ^pxRetain=True means don't clear the abort
                                                          //Putting the xlAbort at the start seems to work better then putting it at the end of the function.  
                                                          //Check inside the main iteration loop also works but risks slowing down the calculation.

            //XlCall.Excel(XlCall.xlcEcho, false);
            //XlCall.Excel(XlCall.xlcEcho, true);

            //New Worksheet
            //1	New workbook with one worksheet
            //5	New workbook with 16 worksheets or based on the default workbook
            //6	New workbook with one Visual Basic module

            XlCall.Excel(XlCall.xlcNew, 6);

            //New Workbook
            //1	Worksheet;
            //6	Microsoft Excel Visual Basic Module
            ret = XlCall.Excel(XlCall.xlcWorkbookInsert, 1);

            ret = XlCall.Excel(XlCall.xlfGetDocument, 76);  //76 active worksheet，88 active workbook
            ret = XlCall.Excel(XlCall.xlcWorkbookName, ret, "testnewnamw");  //rename worksheet
            //saving the workbook, try xlcSaveAs.
            object[,] values = new object[,] { { 1, 2 }, { 3, 4 }, { 5, 6 } };
            int rows = values.GetLength(0);
            int cols = values.GetLength(1);
            ExcelReference target = new ExcelReference(0, rows - 1, 0, cols - 1);
            target.SetValue(values);

            ret = XlCall.Excel(XlCall.xlcAlert, "Delete data!");

            //Get UsedRange
            double firstRow = (double)XlCall.Excel(XlCall.xlfGetDocument, 9);
            double lastRow = (double)XlCall.Excel(XlCall.xlfGetDocument, 10);
            double firstCol = (double)XlCall.Excel(XlCall.xlfGetDocument, 11);
            double lastCol = (double)XlCall.Excel(XlCall.xlfGetDocument, 12);

            //XlCall.Excel(XlCall.xlcSelectEnd, 2);  
            //XlCall.Excel(XlCall.xlcSelectEnd, 4);

            ////The XlCall.xlcMessage call to update the Workbok status-bar makes the Excel Window blink.
            ret = XlCall.Excel(XlCall.xlcMessage, true, firstRow.ToString()
                               + "," + lastRow.ToString()
                               + "," + firstCol.ToString()
                               + "," + lastCol.ToString());

            XlCall.Excel(XlCall.xlcSelectSpecial, 5); //option 5 (Current Region)
            ret = XlCall.Excel(XlCall.xlcClear);      //clear selection range

            //XlCall.Excel(XlCall.xlcFileClose, false);


            /*
            ExcelReference xlRef = new ExcelReference(1, 4, 0, 1);
            int rows = xlRef.RowLast - xlRef.RowFirst + 1;
            int cols = xlRef.ColumnLast - xlRef.ColumnFirst + 1;
            object[,] values = new object[rows, cols]; // nulls
            xlRef.SetValue(values);
             */

        }
        /// <summary>
        /// ExcelReference to Range
        /// </summary>
        /// <param name="xlref"></param>
        /// <returns></returns>
        private static dynamic ReferenceToRange(ExcelReference xlref)          //简版的
        {
            dynamic app = ExcelDnaUtil.Application;
            return app.Range[XlCall.Excel(XlCall.xlfReftext, xlref, true)];
        }
    }
}
